package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisDbExecute;
import com.szholly.pro.watergas_server.common.util.StationOnlineCompare;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.plug.form.validation.ValidationEditInterceptor;

@Service
public class CompanyInfoService {
	
	/**
	 * 查询企业列表
	 * @return
	 */
	public List<RowBase> queryCompanyList(){
		String polluteType = RequestUnit.getParameter("WATER_TYPE");
		String name = RequestUnit.getParameter("NAME");
		String Exp_No = RequestUnit.getParameter("Exp_No");
		String sqlCondition = "";
		String sql = "select t1.company_code,\n"
						+"t1.exp_no,\n"
						+"t1.guid,\n"
						+ "	t1.name as SDENAME,\n"
						+ "	t1.img,t1.temp_img,wmsys.wm_concat(t1.stationInfo) stationInfo,\n"
						+ "	wmsys.wm_concat(scs) scs,\n"
						+ " sum(wwtpStatus) wwtpStatus,\n"
						+ "	wmsys.wm_concat(mns) mns from (\n" +
						"  select w.guid,w.company_code,w.exp_no,w.name,flow_type,w.img,w.temp_img,\n" + 
						" 'ft' || to_char(ms.flow_type) || '_s' || to_char(dc.status) stationInfo,\n"+
						" 'ft' || to_char(ms.flow_type) || '_mn' || to_char(dc.DEVICE_NUM) mns,\n"+
						" 'ft' || to_char(ms.flow_type) || '_sc' || to_char(dc.SITE_ID) scs,\n"+
						"  w.order_num, \n"+
						"  case to_number(dc.status) when null then 0 else to_number(dc.status) end wwtpStatus \n"+
						"  from BC_ENTERPRISE_ATTR w \n" + 
						"         left join BC_SITE_INFO ms on w.exp_no = ms.wwtp_id\n" + 
						"         left join BC_COLLECTION_INFO dc on ms.exp_no = dc.site_id\n" +
						"         WHERE 1=1 "
						+ sqlCondition;
		String subSql="";
		
		if (name != null && !name.equals("")) {
			sql = sql + " and w.NAME like '%" + name + "%'";
		}
		if (Exp_No != null && !Exp_No.equals("")) {
			sql = sql + " and w.Exp_No = '" + Exp_No + "'";
		}
		if (polluteType != null && !"".equals(polluteType)) {
			subSql = subSql + " and cpa.poll_type = '" + polluteType + "'";
		}
		sql=sql+" order by w.objectid";
		sql += "       ) t1,company_polltype_asso cpa  "
				+ "where t1.company_code=cpa.company_code\n" + subSql +
				"       group by t1.company_code,t1.guid,t1.exp_no,t1.name,t1.img,t1.temp_img,t1.order_num "
				+ "     order by t1.order_num asc ";
		sql = " SELECT * FROM ( " + sql + ") t2 order by nlssort(t2.SDENAME,'NLS_SORT=SCHINESE_PINYIN_M'), t2.wwtpStatus desc nulls last ";
		
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable table = tableFactory.openTable("BC_ENTERPRISE_ATTR");
		
		List<RowBase> rows = DataUtil.getSelectRows(sql, null,table);
		for(RowBase row : rows){
			String mnsStr = row.getSafeString("MNS");
			if(mnsStr.trim().equals("")){
				continue;
			}
			
			String[] mns = mnsStr.split(",");
			String onLine0="0";
			String onLine1="0";
			
			
			for(String mn : mns){
				String mnStr = mn.replace("ft0_mn", "").replace("ft1_mn", "");
				String channelid = RedisDbExecute.getString("stationInfo:"+mnStr+":deviceInfo:channelId");
				String onResult = "0";
				if(channelid != null && !channelid.trim().equals("")){
					onResult = "1";
				}
				if(mn.startsWith("ft0")){
					onLine0 = onResult;
				}
				if(mn.startsWith("ft1")){
					onLine1 = onResult;
				}
			}
			row.setValue("STATIONINFO","ft0_s"+onLine0+",ft1_s"+onLine1);
		}
		Collections.sort(rows, new StationOnlineCompare());
		return rows;
	}
	/**
	 * 获取企业列表，包含其下包含的站点数
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	public List<RowBase> companyStationCountInfoList(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		String poll_type = RequestUnit.getParameter("poll_type");
		String sql="select pw.name as SDENAME,pw.exp_no as SDEEXP_NO"
				   +",count(*) as CPMPANYCOUNT from BC_ENTERPRISE_ATTR pw"
			       +" left join BC_SITE_INFO ms on pw.code = ms.wwtp_id"
			       +" where  1=1 ";
		if (poll_type !=null && !"".equals(poll_type)) {
			sql += "and pw.poll_type ='" +(poll_type)+"'";
		}
		sql+=" group by pw.name,pw.code,pw.exp_no";
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable table = tableFactory.openTable("BC_ENTERPRISE_ATTR");		
		List<RowBase> rows = DataUtil.getSelectRows(sql, null,table);		
		return rows;
	}
	
	/**
	 * 查询企业详情信息
	 * 
	 * @param req
	 * @return String
	 */
	public List<RowBase> queryCompanyDetail (HttpServletRequest req) {
		List<RowBase> result = null;
		try {
			List<GlobalParameter> parm = new ArrayList<GlobalParameter>();
			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
			ITable pTable = tableFactory.openTable("BC_ENTERPRISE_ATTR");
			String sql = "select t2.name as SDENAME,t2.OBJECTID as SDEOBJECTID,t2.Exp_No as SDEExp_No,t3.name as OPERATORNAME ,t2.* "
					+ " from BC_ENTERPRISE_ATTR t2  "
					+ " left join OPERATIONS_UNIT t3 on t2.OPERATOR=t3.guid "
					+" left join WWTP_ATTENTION t4 on t2.exp_no=t4.wwtp_exp_no "
					+ " where 1=1 ";
			String Exp_No = req.getParameter("Exp_No");
			String guid = req.getParameter("GUID");
			if (Exp_No != null && !Exp_No.equals("")) {
				sql = sql + " and t2.Exp_No = '" + Exp_No + "'";
			}
			
			if(guid != null && !guid.equals("")){
				sql = sql + " and t2.GUID = '" + guid + "'";
			}
			 result = DataUtil.getPageList(sql, parm, pTable);
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	/**
	 * 查询企业污染源类型,一对一，工业废气企业存在一对多。
	 * 
	 * @param req
	 * @return String
	 * @throws Exception 
	 */
	public List<RowBase> queryCompanyPollType (String CompanyCode) throws Exception {
		
		if(StringUtil.IsNullOrEmpty(CompanyCode)){
			throw new Exception("企业Code不能为空");
		}
		
		List<RowBase> rows = null;
		try {
			List<GlobalParameter> parm = new ArrayList<GlobalParameter>();
			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
			ITable pTable = tableFactory.openTable("COMPANY_POLLTYPE_ASSO");
/*			String sql = "select t.poll_type,t.GUID from company_polltype_asso t"
					     +"where t.company_code = '"+CompanyCode+"'";*/
			String sql = "select d.s_domaincode,d.s_domaincaption,"
					+" case when t2.guid is null then"
					+" 0 else 1 end  as checked"
					+ " from DATA_DEFULATDOMAIN d"
				   +" left join company_polltype_asso t2 on d.s_domaincode = t2.poll_type"+ " and t2.company_code = '" +CompanyCode + "'"
				  + " where  d.s_domainname = '监测类型'"
				   +" and d.s_domaincode not in ('1','3')";
			 rows = DataUtil.getPageList(sql, parm,pTable);
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rows;
	}
	/**
	 * 查询企业污染源类型,一对一，工业废气企业存在一对多,只查询勾选上的复选框项。
	 * 
	 * @param req
	 * @return String
	 * @throws Exception 
	 */
	public List<RowBase> queryCompanyPollTypeChecked (String CompanyCode) throws Exception {
		
		if(StringUtil.IsNullOrEmpty(CompanyCode)){
			throw new Exception("企业Code不能为空");
		}
		
		List<RowBase> rows = null;
		try {
			List<GlobalParameter> parm = new ArrayList<GlobalParameter>();
			ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
			ITable pTable = tableFactory.openTable("COMPANY_POLLTYPE_ASSO");
/*			String sql = "select t.poll_type,t.GUID from company_polltype_asso t"
					     +"where t.company_code = '"+CompanyCode+"'";*/
			String sql = "select d.s_domaincode,d.s_domaincaption,"
					+" case when t2.guid is null then"
					+" 0 else 1 end  as checked"
					+ " from DATA_DEFULATDOMAIN d ,"
				   +" company_polltype_asso t2  where  d.s_domaincode = t2.poll_type"+ " and t2.company_code = '" +CompanyCode + "'"
				  + " and  d.s_domainname = '监测类型'";
			 rows = DataUtil.getPageList(sql, parm,pTable);
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rows;
	}
	/**
	 * 保存新增企业信息（BC_ENTERPRISE_ATTR表），
	 * 同时保存污染源类型（一对多的情况）到企业污染类型表中（COMPANY_POLLTYPE_ASSO）。
	 * 
	 */
	
	public void createCompanyInfo() {
		Map<String,String> map = new HashMap<String, String>();
		HashMap<String, Object> parms = RequestUnit.getHashMapParmO();
		String tableName = RequestUnit.getString("tableName");
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		//加入验证逻辑

		if (!parms.containsKey("GUID")) {
			String guid = UUID.randomUUID().toString();
			parms.put("GUID", guid);
		}
		ITable pTable = tableFactory.openTable(tableName);
		ValidationEditInterceptor.validate(pTable, parms,false);
		//校验通过再新增
		DataUtil.Create(tableName, parms);
		String pollTypeStr = RequestUnit.getParameter("pollTypes");
		String EXP_NO=RequestUnit.getParameter("COMPANY_CODE");
		String[] pollTypeArray = pollTypeStr.split(",");
		
		if (pollTypeArray.length>0) {
			for (int i=0;i<pollTypeArray.length;i++) {
				String sql="";
				map.put("POLLTYPE", pollTypeArray[i]);
				String GUID =  UUID.randomUUID().toString();
				sql=" insert into  COMPANY_POLLTYPE_ASSO(COMPANY_CODE,POLL_TYPE,GUID) values('"+EXP_NO+"',"+map.get("POLLTYPE")+",'"+GUID+"')";
				DataUtil.ExecuteUpdateSql(sql, null);
			}
		}
	}
	/**
	 * 更新企业污染类型（COMPANY_POLLTYPE_ASSO表），
	 * 更新企业信息表
	 * @throws Exception 
	 * 
	 */
	public void updateCompanyInfo() throws Exception {
		HashMap<String, Object> parms = RequestUnit.getHashMapParmO();
		String tableName = RequestUnit.getString("tableName");
		String pollTypes = RequestUnit.getString("pollTypes");
		String[] pollTypeArray={};
        if(pollTypes.indexOf(",")!=-1){
        	pollTypeArray = pollTypes.split(",");
        }
		 
		parms.remove("pollTypes");
		
		DataUtil.Update(tableName, parms);
		String EXP_NO=RequestUnit.getParameter("COMPANY_CODE");
		String sql="";
		if (StringUtil.IsNullOrEmpty(EXP_NO)) {
			throw new Exception("企业编号不能为空");
		}
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		ValidationEditInterceptor.validate(pTable, parms,true);
		//1、根据COMPANY_CODE首先删除掉之前的污染源类型
		String SQL = "delete COMPANY_POLLTYPE_ASSO t where 1=1"+" and t.company_code='" +EXP_NO +"'";
		DataUtil.ExecuteUpdateSql(SQL, null);
		//2、再插入新的污染类型。
		if (pollTypeArray.length>0) {
			for (int i = 0;i < pollTypeArray.length; i++) {
				String type = pollTypeArray[i];
				String GUID =  UUID.randomUUID().toString();
				sql=" insert into  COMPANY_POLLTYPE_ASSO(COMPANY_CODE,POLL_TYPE,GUID) values('"+EXP_NO+"',"+type+",'"+GUID+"')";
				DataUtil.ExecuteUpdateSql(sql, null);
			}
		}
		else{
			String type = pollTypes;
			String GUID =  UUID.randomUUID().toString();
			sql=" insert into  COMPANY_POLLTYPE_ASSO(COMPANY_CODE,POLL_TYPE,GUID) values('"+EXP_NO+"',"+type+",'"+GUID+"')";
			DataUtil.ExecuteUpdateSql(sql, null);
		}
  }
}
